﻿Imports System.Data.OleDb

Public Class CommentGateway
    Inherits DataGateway

    Protected Overrides ReadOnly Property FindAllQuery As String
        Get
            Return "SELECT * FROM AquariumCommentsView"
        End Get
    End Property

    Protected Overrides ReadOnly Property DeleteQuery As String
        Get
            Return "DELETE FROM AquariumComment"
        End Get
    End Property

    Protected Overrides ReadOnly Property SiteUserQuery() As String
        Get
            Return "SELECT SiteUser.* " &
                   "FROM AquariumComment LEFT JOIN SiteUser " &
                   "ON AquariumComment.UserID=SiteUser.ID " &
                   "WHERE AquariumComment.ID=@ID"
        End Get
    End Property

    Public Function Insert(ByVal aquariumID As ULong, ByVal title As String, ByVal description As String, ByVal userID As ULong) As ULong
        Dim newID As ULong
        Dim sqlQuery As String = "INSERT INTO AquariumComment (AquariumID, Title, Description, UserID) " &
                                 "VALUES (@AquariumID, @Title, @Description, @UserID)"
        Using connection As New OleDbConnection(ConnectionString)
            Using command As New OleDbCommand(sqlQuery, connection)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@AquariumID", aquariumID)
                command.Parameters.AddWithValue("@Title", title)
                command.Parameters.AddWithValue("@Description", description)
                command.Parameters.AddWithValue("@UserID", userID)
                connection.Open()
                command.ExecuteNonQuery()
                newID = GetLastInsertID(command)
            End Using
        End Using

        Return newID
    End Function
End Class
